package com.timedomain.him.remake.utils;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.timedomain.him.remake.models.ChatMessage;
import com.timedomain.him.remake.models.CompanionPhoneCall;
import com.timedomain.him.remake.models.MorningCall;

import org.jetbrains.annotations.NotNull;
import org.jetbrains.annotations.Nullable;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

public class DB{

    private static DBHelper dbHelper = null;

    public static void ensureDBInited(Context ctx){
        if(dbHelper == null){
            dbHelper = new DBHelper(ctx);
        }
    }

    public static class ChatMessage{
        public static void Add(com.timedomain.him.remake.models.ChatMessage chatMessage){
            if(chatMessage.type < -2){
                HLog.w("ChatMessageDB","This type "+chatMessage.type+" should never be stored in database");
                return;
            }
            try(SQLiteDatabase db = dbHelper.getWritableDatabase()){
                db.execSQL("Insert INTO chat(\"date\",\"content\",\"audioFile\",\"type\",\"extra\") VALUES (?,?,?,?,?)",
                        new Object[]{
                                chatMessage.date,
                                chatMessage.content,
                                chatMessage.audioFile,
                                chatMessage.type,
                                chatMessage.extra
                        }
                );
            }
        }
        
        public static List<com.timedomain.him.remake.models.ChatMessage> dumpData(){
            List<com.timedomain.him.remake.models.ChatMessage> result = new ArrayList<>();
            String sql = "select \"id\",\"date\",\"content\",\"audioFile\",\"type\",\"extra\" from chat order by date desc limit 512";
            try(SQLiteDatabase db = dbHelper.getReadableDatabase()){
                try(Cursor cursor = db.rawQuery(sql,new String[0])){
                    if(cursor.moveToFirst()){
                        do {
                            int id = cursor.getInt(0);
                            long date = cursor.getLong(1);
                            String content = cursor.getString(2);
                            String audioFile = cursor.getString(3);
                            int type = cursor.getInt(4);
                            String extra = cursor.getString(5);
                            result.add(new com.timedomain.him.remake.models.ChatMessage(
                                    id,date,content,type,audioFile,extra
                            ));

                        }while (cursor.moveToNext());
                    }
                }
            }
            return result;

        }

        public static void restoreData(List<com.timedomain.him.remake.models.ChatMessage> data){
            for (com.timedomain.him.remake.models.ChatMessage item :
                    data) {
                Add(item);
            }
        }
        
        public static List<com.timedomain.him.remake.models.ChatMessage> Query(int skip){
            String sql = "select \"id\",\"date\",\"content\",\"audioFile\",\"type\",\"extra\" from chat order by date desc limit 20 offset "+skip;

            List<com.timedomain.him.remake.models.ChatMessage> result = new ArrayList<>();
            try(SQLiteDatabase db = dbHelper.getReadableDatabase()){
                try(Cursor cursor = db.rawQuery(sql,new String[0])){
                    if(cursor.moveToFirst()){
                        do {
                            int id = cursor.getInt(0);
                            long date = cursor.getLong(1);
                            String content = cursor.getString(2);
                            String audioFile = cursor.getString(3);
                            int type = cursor.getInt(4);
                            String extra = cursor.getString(5);
                            result.add(new com.timedomain.him.remake.models.ChatMessage(
                                    id,date,content,type,audioFile,extra
                            ));

                        }while (cursor.moveToNext());
                    }
                }
            }
            return result;
        }

        public static void Clear() {
            String sql = "delete from chat";
            try(SQLiteDatabase db = dbHelper.getWritableDatabase()){
                db.execSQL(sql);
            }
        }
    }

    public static class CompanionPhoneCall{
        public static void Add(com.timedomain.him.remake.models.CompanionPhoneCall companionPhoneCall){
            try(SQLiteDatabase db = dbHelper.getWritableDatabase()){
                int count = 0;
                try(Cursor c = db.rawQuery("select * from companion where path = ?",new String[]{companionPhoneCall.path})){
                    count = c.getCount();
                }
                if(count == 0){
                    db.execSQL("Insert INTO companion(\"date\",\"title\",\"duration\",\"path\") VALUES (?,?,?,?)",
                            new Object[]{
                                    companionPhoneCall.date,
                                    companionPhoneCall.title,
                                    companionPhoneCall.duration,
                                    companionPhoneCall.path
                            }
                    );
                }
                else{
                    db.execSQL("update companion set date = ? where path = ?",
                            new Object[]{
                                    companionPhoneCall.date,
                                    companionPhoneCall.path
                            }
                    );
                }

            }
        }

        public static List<com.timedomain.him.remake.models.CompanionPhoneCall> Query(){
            String sql = "select \"id\",\"date\",\"title\",\"duration\",\"path\" from companion order by date desc";

            List<com.timedomain.him.remake.models.CompanionPhoneCall> result = new ArrayList<>();
            try(SQLiteDatabase db = dbHelper.getReadableDatabase()){
                try(Cursor cursor = db.rawQuery(sql,new String[0])){
                    if(cursor.moveToFirst()){
                        do {
                            int id = cursor.getInt(0);
                            long date = cursor.getLong(1);
                            String title = cursor.getString(2);
                            String duration = cursor.getString(3);
                            String path = cursor.getString(4);
                            result.add(new com.timedomain.him.remake.models.CompanionPhoneCall(
                                    id,title,path,date,duration
                            ));

                        }while (cursor.moveToNext());
                    }
                }
            }
            return result;
        }

        public static List<com.timedomain.him.remake.models.CompanionPhoneCall> dumpData(){
            return Query();
        }

        public static void restoreData(List<com.timedomain.him.remake.models.CompanionPhoneCall> data){
            for (com.timedomain.him.remake.models.CompanionPhoneCall item :
                    data) {
                Add(item);
            }
        }
        public static void Clear(){
            String sql = "delete from companion";
            try(SQLiteDatabase db = dbHelper.getWritableDatabase()){
                db.execSQL(sql);
            }
        }
    }

    public static class MorningCall{

        public static void Add(com.timedomain.him.remake.models.MorningCall morningCall){
            try(SQLiteDatabase db = dbHelper.getWritableDatabase()){
                db.execSQL("Insert INTO morningcall(\"date\",\"title\",\"duration\",\"time\",\"fav\",\"path\") VALUES (?,?,?,?,?,?)",
                    new Object[]{
                            morningCall.date,
                            morningCall.title,
                            morningCall.duration,
                            morningCall.time,
                            morningCall.fav ? 1 : 0,
                            morningCall.path
                    }
                );
            }
        }

        public static void Clear(){
            String sql = "delete from morningcall";
            try(SQLiteDatabase db = dbHelper.getWritableDatabase()){
                db.execSQL(sql);
            }
        }

        public static List<com.timedomain.him.remake.models.MorningCall> Query(int skip,boolean favOnly){
            String sql = "select \"id\",\"date\",\"title\",\"duration\",\"time\",\"fav\",\"path\" from morningcall order by date desc limit 20 offset "+skip;
            if(favOnly){
                sql = "select \"id\",\"date\",\"title\",\"duration\",\"time\",\"fav\",\"path\" from morningcall where fav <> 0 order by date desc limit 20 offset "+skip;
            }
            List<com.timedomain.him.remake.models.MorningCall> result = new ArrayList<>();
            try(SQLiteDatabase db = dbHelper.getReadableDatabase()){
                try(Cursor cursor = db.rawQuery(sql,new String[0])){
                    if(cursor.moveToFirst()){
                        do {
                            int id = cursor.getInt(0);
                            long date = cursor.getLong(1);
                            String title = cursor.getString(2);
                            String duration = cursor.getString(3);
                            String time = cursor.getString(4);
                            boolean fav = cursor.getInt(5) != 0;
                            String path = cursor.getString(6);
                            result.add(new com.timedomain.him.remake.models.MorningCall(
                               id,date,title,duration,time,fav,path
                            ));

                        }while (cursor.moveToNext());
                    }
                }
            }
            return result;
        }

        public static void setFav(int id,boolean fav){
            String sql = "update morningcall set fav = ? where id = ?";
            try(SQLiteDatabase db = dbHelper.getWritableDatabase()){
                db.execSQL(sql,
                        new Object[]{
                                fav ? 1 : 0,
                                id
                        }
                );
            }
        }

        public static List<com.timedomain.him.remake.models.MorningCall> dumpData(){
            String sql = "select \"id\",\"date\",\"title\",\"duration\",\"time\",\"fav\",\"path\" from morningcall order by date desc";
            List<com.timedomain.him.remake.models.MorningCall> result = new ArrayList<>();
            try(SQLiteDatabase db = dbHelper.getReadableDatabase()){
                try(Cursor cursor = db.rawQuery(sql,new String[0])){
                    if(cursor.moveToFirst()){
                        do {
                            int id = cursor.getInt(0);
                            long date = cursor.getLong(1);
                            String title = cursor.getString(2);
                            String duration = cursor.getString(3);
                            String time = cursor.getString(4);
                            boolean fav = cursor.getInt(5) != 0;
                            String path = cursor.getString(6);
                            result.add(new com.timedomain.him.remake.models.MorningCall(
                                    id,date,title,duration,time,fav,path
                            ));

                        }while (cursor.moveToNext());
                    }
                }
            }
            return result;
        }
        public static void restoreData(List<com.timedomain.him.remake.models.MorningCall> data){
            for (com.timedomain.him.remake.models.MorningCall item :
                    data) {
                Add(item);
            }
        }
    }

    public static class DBHelper extends SQLiteOpenHelper{

        String createMorningCallDB = "CREATE TABLE \"morningcall\" (\n" +
                "\t\"id\"\tINTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,\n" +
                "\t\"date\"\tINTEGER NOT NULL,\n" +
                "\t\"title\"\tTEXT,\n" +
                "\t\"duration\"\tTEXT,\n" +
                "\t\"time\"\tTEXT,\n" +
                "\t\"fav\"\tINTEGER NOT NULL DEFAULT 0,\n" +
                "\t\"path\"\tTEXT\n" +
                ");";

        String createCompanionCallDB = "CREATE TABLE \"companion\" (\n" +
                "\t\"id\"\tINTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,\n" +
                "\t\"date\"\tINTEGER NOT NULL,\n" +
                "\t\"title\"\tTEXT,\n" +
                "\t\"duration\"\tTEXT,\n" +
                "\t\"path\"\tTEXT,\n" +
                "\t\"extra\"\tTEXT\n" +
                ");";

        String createChatDB = "CREATE TABLE \"chat\" (\n" +
                "\t\"id\"\tINTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,\n" +
                "\t\"date\"\tINTEGER NOT NULL,\n" +
                "\t\"content\"\tTEXT,\n" +
                "\t\"audioFile\"\tTEXT,\n" +
                "\t\"extra\"\tTEXT,\n" +
                "\t\"type\"\tINTEGER NOT NULL DEFAULT 0\n" +
                ");";

        private static final int DB_VERSION = 2;

        public DBHelper(@Nullable Context context) {
            super(context, "appdata.db", null,DB_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            onUpgrade(db,0,DB_VERSION);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            for (int i = oldVersion+1; i <=newVersion ; i++) {

                switch (i){
                    case 1:
                        db.execSQL(createMorningCallDB);
                        break;
                    case 2:
                        db.execSQL(createChatDB);
                        db.execSQL(createCompanionCallDB);
                        break;
                }
            }
        }
    }

}
